package com.cqndt.disaster.device.util;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;


/**
 * excel 导入工具类
 * @author yin_q
 */
public class ImportExcelUtil {
	
	private final static String excel2003L =".xls";    //2003- 版本的excel
    private final static String excel2007U =".xlsx";   //2007+ 版本的excel

    /**
     * 描述：获取IO流中的数据，组装成List<List<Object>>对象
     * @param in,fileName
     * @return
     * @throws IOException 
     */
    public static List<List<Object>> getBankListByExcel(InputStream in,String fileName) throws Exception{
        List<List<Object>> list = null;

        //创建Excel工作薄
        Workbook work = getWorkbook(in,fileName);
        if(null == work){
            throw new Exception("创建Excel工作薄为空！");
        }
        Sheet sheet = null;
        Row row = null;
        Cell cell = null;

        list = new ArrayList<List<Object>>();
        if(work.getNumberOfSheets() > 0){
			//遍历Excel中所有的sheet
			sheet = work.getSheetAt(0);
			//遍历当前sheet中的所有行
			int firstRowNum=sheet.getFirstRowNum()+1;
			Row firstRow=sheet.getRow(firstRowNum);
			int count=firstRow.getLastCellNum();

			System.err.println(count);
			for (int j = sheet.getFirstRowNum()+2; j <= sheet.getLastRowNum(); j++) {
				row = sheet.getRow(j);
				if(row==null||row.getFirstCellNum()==j){continue;}
				//遍历所有的列
				List<Object> li = new ArrayList<Object>();
				for (int y = row.getFirstCellNum(); y < count; y++) {
					cell = row.getCell(y);
					if(cell == null){
						li.add("0");
						continue;
					}
					li.add(getCellValue(cell));
				}
				list.add(li);
			}
        }
        work.close();
        return list;
    }
    
    /**
	 * 描述：根据文件后缀，自适应上传文件的版本
	 * 
	 * @param inStr,fileName
	 * @return
	 * @throws Exception
	 */
	private static Workbook getWorkbook(InputStream inStr, String fileName) throws Exception {
		Workbook wb = null;
		String fileType = fileName.substring(fileName.lastIndexOf("."));
		if (excel2003L.equals(fileType)) {
			wb = new HSSFWorkbook(inStr); // 2003-
		} else if (excel2007U.equals(fileType)) {
			wb = new XSSFWorkbook(inStr); // 2007+
		} else {
			throw new Exception("解析的文件格式有误！");
		}
		return wb;
	}

    
    /**
     * 描述：对表格中数值进行格式化
     * @param cell
     * @return
     */
	private static Object getCellValue(Cell cell){
        Object value = "";
        DecimalFormat df = new DecimalFormat("0.00000");  //格式化number String字符
//        SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd");  //日期格式化
//        DecimalFormat df2 = new DecimalFormat("0.00");  //格式化数字
		CellType type = cell.getCellTypeEnum();
		switch (type){
			case BLANK:
				value = "null";
				break;
			case BOOLEAN:
				value = String.valueOf(cell.getBooleanCellValue());
				break;
			case ERROR:
				value = "";
				break;
			case FORMULA:
				break;
			case NUMERIC:
				if("General".equals(cell.getCellStyle().getDataFormatString())){
					value = df.format(cell.getNumericCellValue());
				}else if("m/d/yy".equals(cell.getCellStyle().getDataFormatString())){
					value = cell.getDateCellValue();
				}else{
					value = cell.getNumericCellValue();
				}
				break;
			case STRING:
				value = cell.getRichStringCellValue().getString();
				break;
			default:
					value = "";
		}
        return value;
    }

}
